package com.winner.data.util;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;

import com.winner.data.entity.User;

/**
 * jdbc 批处理 执行sql, 造测试数据
 *
 * @author wangxinle5
 * @since 2022-11-11
 */
public class JdbcBatchGenerateTestData {
    static String TABLE_NAME = "user";

    static String URL = "jdbc:mysql://localhost:3306/test";

    static String USER_NAME = "root";

    static String PASSWORD = "123456";

    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = DriverManager.getConnection(URL, USER_NAME, PASSWORD);
            DatabaseMetaData metaData = connection.getMetaData();
            String catalog = connection.getCatalog();
            String tableName = TABLE_NAME;
            System.out.println("Table Name: " + tableName);

            ResultSet columns = metaData.getColumns(catalog, null, tableName, null);
            StringBuilder insertStatement = new StringBuilder("INSERT INTO " + tableName + " (");
            int count = 0;
            ArrayList<String> filedList = new ArrayList<>();
            while (columns.next()) {
                String columnName = columns.getString("COLUMN_NAME");
                filedList.add(columnName);
                if (count > 0) {
                    insertStatement.append(", ");
                }
                insertStatement.append(columnName);
                count++;
            }
            insertStatement.append(") VALUES (");
            System.out.println("表的字段个数是 " + count + "个");
            for (int i = 0; i < count; i++) {
                if (i == 0) {
                    insertStatement.append("?, ");
                } else if (i == count - 1) {
                    insertStatement.append("?");
                } else {
                    insertStatement.append("").append("?, ").append("");
                }

            }
            insertStatement.append(")");
            System.out.println("单表新增的sql是 :\n" + insertStatement);

            // 设置自动提交为 false，以便进行事务控制
            connection.setAutoCommit(false);
            // 创建 PreparedStatement 对象
            preparedStatement = connection.prepareStatement(String.valueOf(insertStatement));

            ArrayList<User> list = new ArrayList<>();
            for (int i = 1; i <= 10000; i++) {
                User user = new User();
                // 因为表是自增的,这里可不设置id user.setId((long) i);
                user.setGender("M");
                user.setName("zhangsan");
                user.setBirthdate(new Date());
                user.setUserCard("610502199110210813");
                list.add(user);
            }
            // 将用户数据添加到批处理中
            for (User user : list) {
                preparedStatement.setObject(1, user.getId());
                preparedStatement.setObject(2, user.getGender());
                preparedStatement.setObject(3, user.getName());
                preparedStatement.setObject(4, user.getBirthdate());
                preparedStatement.setObject(5, user.getUserCard());
                preparedStatement.addBatch();
            }
            // 执行批处理
            preparedStatement.executeBatch();
            // 提交事务
            connection.commit();
            // Step 3: Close the connection
            connection.close();
        } catch (SQLException e) {
            // 处理异常，回滚事务
            if (connection != null) {
                try {
                    connection.rollback();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
            e.printStackTrace();
        } finally {
            // 关闭资源
            if (preparedStatement != null) {
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        System.out.println("----------批量产生测试数据 end--------------");
    }

}
